package com.ice.base.excel;

import cn.hutool.core.io.IoUtil;
import cn.hutool.poi.excel.ExcelUtil;
import cn.hutool.poi.excel.ExcelWriter;
import com.github.pagehelper.PageHelper;
import com.ice.base.controller.BaseController;
import com.ice.body.business.model.UserModel;
import com.ice.body.business.service.UserService;
import io.swagger.annotations.ApiOperation;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import springfox.documentation.annotations.ApiIgnore;

import javax.annotation.Resource;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.UnsupportedEncodingException;
import java.util.List;

/**
 * Created with GuoYanBin.
 * Description:
 * Date: 2021-03-17
 * Time: 10:44
 */
@Slf4j
@RestController
@ApiIgnore(value = "Excel样板")
public class ExcelTemplate extends BaseController {
    //---------------------------用户表-----------------------start
    @Resource
    private UserService userService;


    @ApiOperation(value = "用户表-导出到response")
    @GetMapping("/api_p/User/exportUser")
    public void exportUser(HttpServletResponse response, @RequestParam(name = "pageSize") Integer pageSize,
                           @RequestParam(name = "pageNum") Integer pageNum,
                           UserModel model) {
        try {
            PageHelper.startPage(pageNum, pageSize);

            List<UserModel> list = userService.selectListByModel(model);

            // 通过工具类创建writer
            ExcelWriter writer = ExcelUtil.getWriter();
            // 合并单元格后的标题行，使用默认标题样式
            writer.merge(13, "用户数据");
            // 一次性写出内容，使用默认样式，强制输出标题
            writer.write(list, true);
            response.setContentType("application/vnd.ms-excel;charset=utf-8");
            //test.xls是弹出下载对话框的文件名，不能为中文，中文请自行编码
            String fileName = new String("用户数据.xls".getBytes(), "ISO8859-1");
            response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
            ServletOutputStream out = response.getOutputStream();
            writer.flush(out, true);
            // 关闭writer，释放内存
            writer.close();
            //此处记得关闭输出Servlet流
            IoUtil.close(out);

        } catch (Exception e) {
            e.printStackTrace();
        }
    }

/*
//自定义标题别名
        writer.addHeaderAlias("name", "姓名");
        writer.addHeaderAlias("age", "年龄");
        writer.addHeaderAlias("score", "分数");
        writer.addHeaderAlias("isPass", "是否通过");
        writer.addHeaderAlias("examDate", "考试时间");

// 合并单元格后的标题行，使用默认标题样式
        writer.merge(4, "一班成绩单");
// 一次性写出内容，使用默认样式，强制输出标题
        writer.write(rows, true);
// 关闭writer，释放内存
        writer.close();*/


    /**
     * 手写导出
     * @param response
     * @param fileName
     */

  /*  @ApiOperation(value = "数据上报-数据导出(管理端)")
    @PostMapping("/api/Report/reportExport")
    public void reportExport(HttpServletResponse response,
                             @RequestParam(name = "pageSize") Integer pageSize,
                             @RequestParam(name = "pageNum") Integer pageNum,
                             ReportModel model){
        try{
            PageHelper.startPage(pageNum, pageSize);
            List<ReportModel> list = reportService.selectManagerList(model);

            //excel标题
            String[] title = {"序号","问题编号","问题名称","属性","分类","分类","责任单位","分配时间","内容"};
            //excel文件名
            String fileName = "上报信息表"+System.currentTimeMillis()+".xls";
            //sheet名
            String sheetName = "上报信息表";

            SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

            String [][] content = new String[list.size()][];
            if(null !=list && list.size()>0){
                for (int i = 0; i < list.size(); i++) {
                    content[i] = new String[title.length+1];
                    ReportModel obj = list.get(i);
                    content[i][0] = (i+1)+"";//序号
                    content[i][1] = obj.getReportNo();
                    content[i][2] = obj.getTitle();
                    content[i][3] = obj.getAttributes();
                    content[i][4] = obj.getClassify();
                    content[i][5] = obj.getSite();
                    content[i][6] = obj.getAssignUser();
                    content[i][7] = obj.getContent();
                    content[i][8] =format.format(obj.getAssignTime());//支付时间
                }
            }
            //创建HSSFWorkbook
            HSSFWorkbook wb = getHSSFWorkbook(sheetName, title, content, null);
            //响应到客户端
            try {
                this.setResponseHeader(response, fileName);
                OutputStream os = response.getOutputStream();
                wb.write(os);
                os.flush();
                os.close();
            } catch (Exception e) {
                e.printStackTrace();
            }

        }catch (Exception e){
            e.printStackTrace();
        }
    }*/

    //发送响应流方法
    public void setResponseHeader(HttpServletResponse response, String fileName) {
        try {
            try {
                fileName = new String(fileName.getBytes(),"ISO8859-1");
            } catch (UnsupportedEncodingException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
            response.setContentType("application/octet-stream;charset=ISO8859-1");
            response.setHeader("Content-Disposition", "attachment;filename="+ fileName);
            response.addHeader("Pargam", "no-cache");
            response.addHeader("Cache-Control", "no-cache");
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }

    public static HSSFWorkbook getHSSFWorkbook(String sheetName,String []title,String [][]values, HSSFWorkbook wb){

        // 第一步，创建一个HSSFWorkbook，对应一个Excel文件
        if(wb == null){
            wb = new HSSFWorkbook();
        }

        // 第二步，在workbook中添加一个sheet,对应Excel文件中的sheet
        HSSFSheet sheet = wb.createSheet(sheetName);

        // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制
        HSSFRow row = sheet.createRow(0);

        // 第四步，创建单元格，并设置值表头 设置表头居中
        HSSFCellStyle style = wb.createCellStyle();
        //  style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
        style.setAlignment(HorizontalAlignment.CENTER);
        //声明列对象
        HSSFCell cell = null;

        //创建标题
        for(int i=0;i<title.length;i++){
            cell = row.createCell(i);
            cell.setCellValue(title[i]);
            cell.setCellStyle(style);
        }

        //创建内容
        for(int i=0;i<values.length;i++){
            row = sheet.createRow(i + 1);
            for(int j=0;j<values[i].length;j++){
                //将内容按顺序赋给对应的列对象
                row.createCell(j).setCellValue(values[i][j]);
            }
        }
        return wb;
    }

/*

    //导出excel 带图片
    //http://localhost:8081/api_p/Report/reportExport?pageSize=10&pageNum=1&id=1
    @ApiOperation(value = "数据上报-数据导出(管理端-带图片)")
    @GetMapping("/api_p/Report/reportExport")
    public void reportExport(HttpServletResponse response,
                             @RequestParam(name = "pageSize") Integer pageSize,
                             @RequestParam(name = "pageNum") Integer pageNum,
                             ReportModel model)throws Exception{

        PageHelper.startPage(pageNum, pageSize);
        //获取所有的主推
        List<ReportModel> list = reportService.selectManagerList(model);

        try {
            String[] title = {"序号","问题编号","问题名称","属性","分类","地点","责任单位","内容","图片"};
            //excel名称
            String fileName = "问题数据.xls"; //sheet名

            HSSFWorkbook wb = new HSSFWorkbook();
            HSSFSheet sheet1 = wb.createSheet("sheet1");
            sheet1.setDefaultColumnWidth((short) 20);
            sheet1.setDefaultRowHeight((short) 2000);
            String [][] content = new String[list.size()][];
            String winLocPath = BaseFileConstant.StaticPath.STATIC_UPLOAD.getWinLocPath();
            if(null !=list && list.size()>0){
                for (int i = 0; i < list.size(); i++) {
                    content[i] = new String[title.length+1];
                    ReportModel obj = list.get(i);
                    content[i][0] = (i+1)+"";//序号
                    content[i][1] = obj.getReportNo();
                    content[i][2] = obj.getTitle();
                    content[i][3] = obj.getAttributes();
                    content[i][4] = obj.getClassify();
                    content[i][5] = obj.getSite();
                    content[i][6] = obj.getAssignUser();
                    content[i][7] = obj.getContent();
                    //  content[i][8] =format.format(obj.getAssignTime());//分配时间
                    String imgs = obj.getImgs();
                    if(StringUtils.isNotEmpty(imgs)){
                        String[] imgArray = imgs.split(",");
                        for(int j = 0 ;j<imgArray.length;j++){
                            ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
                            BufferedImage bufferImg = null;
                            */
/* URL url = new URL(imgArray[j]);*//*

                            //获取文件后缀名
                            String suffix = imgArray[j].substring(imgArray[j].lastIndexOf(".") + 1);
                            String pName = imgArray[j].substring(imgArray[j].lastIndexOf("/") + 1);
                            pName = winLocPath+pName;
                            */
/*  bufferImg = ImageIO.read(url);*//*


                            bufferImg = ImageIO.read(new File(pName));
                            ImageIO.write(bufferImg, suffix, byteArrayOut);

                            //画图的顶级管理器，一个sheet只能获取一个（一定要注意这点）
                            HSSFPatriarch patriarch = sheet1.createDrawingPatriarch();
                            //anchor主要用于设置图片的属性
                            HSSFClientAnchor anchor = new HSSFClientAnchor(0, //x缩放
                                    0, // y缩放
                                    1023, //最大1023
                                    255, //最大255
                                    (short) (8+j),  //于下下个参数进行定位 0开始
                                    i+1, //在第几行
                                    (short) (8+j), //宽度占几格 0开始
                                    i+1 //第几列
                            );
                            //插入图片
                            patriarch.createPicture(anchor, wb.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
                            bufferImg.flush();
                            byteArrayOut.flush();
                        }
                    }
                }
            }
            //创建HSSFWorkbook
            wb = getHSSFWorkbook(sheet1, title, content, wb);
            //响应到客户端

            this.setResponseHeader(response, fileName);
            OutputStream os = response.getOutputStream();
            wb.write(os);
            os.flush();
            os.close();
        } catch (Exception e) {
            e.printStackTrace();
            System.err.println(e.getMessage());
        }finally{

        }
    }

    //发送响应流方法
    public void setResponseHeader(HttpServletResponse response, String fileName) {
        try {
            try {
                fileName = new String(fileName.getBytes(),"ISO8859-1");
            } catch (UnsupportedEncodingException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
            response.setContentType("application/octet-stream;charset=ISO8859-1");
            response.setHeader("Content-Disposition", "attachment;filename="+ fileName);
            response.addHeader("Pargam", "no-cache");
            response.addHeader("Cache-Control", "no-cache");
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }

    public static HSSFWorkbook getHSSFWorkbook(HSSFSheet sheet,String []title,String [][]values, HSSFWorkbook wb){



        // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制
        HSSFRow row = sheet.createRow(0);

        // 第四步，创建单元格，并设置值表头 设置表头居中
        HSSFCellStyle style = wb.createCellStyle();
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中

        HSSFCellStyle style2 = wb.createCellStyle();
        style2.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
        style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中
        //声明列对象
        HSSFCell cell = null;

        //设置字体和内容位置
        HSSFFont f = wb.createFont();
        f.setFontHeightInPoints((short) 11);// 字号
        f.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 加粗
        style.setFont(f);
        //创建标题
        for(int i=0;i<title.length;i++){
            cell = row.createCell(i);
            cell.setCellValue(title[i]);
            cell.setCellStyle(style);
        }

        //创建内容
        for(int i=0;i<values.length;i++){
            row = sheet.createRow(i + 1);
            for(int j=0;j<values[i].length;j++){
                //将内容按顺序赋给对应的列对象
                HSSFCell cell1 = row.createCell(j);
                cell1.setCellValue(values[i][j]);
                cell1.setCellStyle(style2);
            }
        }
        return wb;
    }


*/


}